{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bcce5100-f691-4db3-8f11-7f265e5a28be",
   "metadata": {},
   "source": [
    "# 数据预处理\n",
    "## 举例多源数据ETL简单处理过程\n",
    "现在有两个文件，sales.csv和users.json <br>\n",
    "我们会通过处理，来合并这两份数据。从而简单的演示一下ETL流程。<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d133933a-cf09-4cc3-baae-643ee573fe0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "# ========== E：Extract（抽取） ==========\n",
    "# 1. 读取 CSV 文件（销售数据）\n",
    "sales_df = pd.read_csv(\"sales.csv\")\n",
    "\n",
    "# 2. 读取 JSON 文件（用户数据）\n",
    "f=open(\"users.json\", \"r\", encoding=\"utf-8\")\n",
    "users_data = json.load(f)\n",
    "users_df = pd.json_normalize(users_data)\n",
    "\n",
    "print(\"=== 原始数据预览 ===\")\n",
    "print(sales_df.head(), \"\\n\")\n",
    "print(users_df.head(), \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38e58d12-edbb-4a32-b1c5-ea2f8999727d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ========== T：Transform（转换） ==========\n",
    "# 1. 统一字段名称\n",
    "users_df.rename(columns={\"id\": \"uid\"}, inplace=True)\n",
    "\n",
    "# 2. 转换数据类型\n",
    "sales_df[\"price\"] = sales_df[\"price\"].astype(float)\n",
    "users_df[\"age\"] = users_df[\"age\"].astype(int)\n",
    "sales_df[\"date\"] = pd.to_datetime(sales_df[\"date\"])\n",
    "\n",
    "# 3. 处理缺失值（若有）\n",
    "sales_df.fillna({\"price\": 0}, inplace=True)\n",
    "\n",
    "# 4. 合并两份数据\n",
    "merged_df = pd.merge(sales_df, users_df, on=\"uid\", how=\"left\")\n",
    "\n",
    "# 5. 新增计算字段\n",
    "merged_df[\"year_month\"] = merged_df[\"date\"].dt.to_period(\"M\").astype(str)\n",
    "merged_df[\"price_with_tax\"] = merged_df[\"price\"] * 1.1  # 含税价\n",
    "\n",
    "# 6. 清洗与排序\n",
    "merged_df.sort_values(by=[\"date\"], inplace=True)\n",
    "merged_df.reset_index(drop=True, inplace=True)\n",
    "\n",
    "print(\"=== 转换后数据 ===\")\n",
    "print(merged_df.head(), \"\\n\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91d3578d-9bc2-47fd-8b36-ebb52250f796",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ========== L：Load（加载） ==========\n",
    "# 1. 保存为 Parquet 格式（高效压缩格式）\n",
    "#merged_df.to_parquet(\"clean_sales.parquet\", index=False)\n",
    "\n",
    "# 2. 可选：保存为 CSV\n",
    "merged_df.to_csv(\"clean_sales.csv\", index=False, encoding=\"utf-8-sig\")\n",
    "\n",
    "print(\"ETL 处理完成 ✅ 文件已保存为 clean_sales.parquet / clean_sales.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8877e4d-0850-45a4-a3d2-44fd210616ee",
   "metadata": {},
   "source": [
    "# pandas中数据读取\n",
    "1. 读取csv文件参考class2中的内容\n",
    "2. 读取json文件\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a41a019d-9541-4c66-945a-8b439898c597",
   "metadata": {},
   "source": [
    "1. read_json()方法：pandas.read_json(path_or_buf, orient=None, typ='frame', lines=False, ...)</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "900f4900-ad6c-4b0f-b2a3-c98d5e5c4309",
   "metadata": {},
   "source": [
    "- orient：指定 JSON 的数据布局（方向）'records' （[{\"a\":1,\"b\":2},{\"a\":3,\"b\":4}]）, 'columns'{{a:1},{b:2}}, 'index‘{a:{a:1},b:{b:2}}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "895c04b9-3f87-4da8-98f0-50f80108f4f0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "users_df1 = pd.read_json(\"users.json\")\n",
    "print(users_df1.head(), \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3325e61c-ac15-43a5-ac70-1d892d92aed1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#如果api返回的是json格式\n",
    "df = pd.read_json(\"https://api.exchangerate-api.com/v4/latest/USD\")\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db4feac4-f3f4-4ae8-8535-2fafb08d7142",
   "metadata": {},
   "source": [
    "2. json_normalize():用于把嵌套的 JSON 数据结构“扁平化”成表格形式（DataFrame）。\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acdc3025-b386-4a3c-913b-aba9b5c8476b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = [\n",
    "    {\n",
    "        \"id\": 1,\n",
    "        \"name\": \"Alice\",\n",
    "        \"orders\": [\n",
    "            {\"order_id\": 101, \"product\": \"Laptop\"},\n",
    "            {\"order_id\": 102, \"product\": \"Mouse\"}\n",
    "        ]\n",
    "    },\n",
    "    {\n",
    "        \"id\": 2,\n",
    "        \"name\": \"Bob\",\n",
    "        \"orders\": [\n",
    "            {\"order_id\": 201, \"product\": \"Keyboard\"}\n",
    "        ]\n",
    "    }\n",
    "]\n",
    "\n",
    "df = pd.json_normalize(\n",
    "    data, \n",
    "    record_path='orders', \n",
    "    meta=['id', 'name'], \n",
    "    sep ='_',\n",
    "\n",
    ")\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "759d69dd-7e88-4229-92dc-170b6359898a",
   "metadata": {},
   "source": [
    "其中</p>\n",
    "- record_path：如果 JSON 中某个键对应的是一个列表（数组），你想把它展开为多行数据。\n",
    "- meta:  用于保留上层的键作为附加列。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2e3e916-93f7-4f93-b030-7da727404c71",
   "metadata": {},
   "source": [
    "# pandas中的数据转换"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bee39bf1-314c-4da9-bf1e-8a6202e49080",
   "metadata": {},
   "source": [
    "## 1. 类型转换\n",
    "读取数据以后会识别成不正确的类型，比如把数值识别成字符串等。<br>\n",
    "pandas和python中的数据类型对比<br>\n",
    "参考https://juejin.cn/post/7131281530898350088"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6566b576-f8ad-4f03-8276-ee68cfafa4a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"id\": [\"1001\", \"1002\"],\n",
    "    \"price\": [\"35.5\", \"42.0\"],\n",
    "    \"date\": [\"2025-01-01\", \"2025-01-02\"],\n",
    "    \"category\": [\"Fruit\", \"Veggie\"]\n",
    "})\n",
    "#看起来像数字和日期，但都是字符串\n",
    "print(df.dtypes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f749f4e-848b-44fd-9c80-0e9917565199",
   "metadata": {},
   "source": [
    "### astype() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a169735b-b5d0-447c-98af-84aa9fa53d26",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"id\"] = df[\"id\"].astype(int)\n",
    "df[\"price\"] = df[\"price\"].astype(float)\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17553c1a-946b-4b96-8e85-dedf7e502a66",
   "metadata": {},
   "source": [
    "### pd.to_numeric()\n",
    "- 默认errors='raise' 遇错报异常，常用 errors='coerce' 遇错转为 NaN (Not a Number)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5915a03-9d38-477b-88d0-d5c5b1ac6200",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = ['10', '20', 'invalid_value', '30']\n",
    "series = pd.Series(data)\n",
    "\n",
    "# Convert to numeric with errors='coerce'\n",
    "numeric_series = pd.to_numeric(series, errors='coerce')\n",
    "\n",
    "numeric_series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d73d4fe-a09f-49f7-baa9-1b856f89db2d",
   "metadata": {},
   "source": [
    "### pd.to_datetime()\n",
    "- 支持多种格式，如 \"2025/01/01\"、\"2025.01.01\" 都能自动识别，也可指定格式。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecacc647-4c40-4966-b7b1-2b9dabdd2339",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"date\": [\"2025-01-01\", \"2025-01-02\"]\n",
    "pd.to_datetime(df[\"date\"], format=\"%Y-%m-%d\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3255fbe7-b9c7-4e12-be40-3319ab496202",
   "metadata": {},
   "source": [
    "### 一次转换多个类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6d4f467-b176-49b9-acb8-ff2f8588dc49",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.astype({\n",
    "    \"id\": \"int64\",\n",
    "    \"price\": \"float64\"\n",
    "})\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce9d4b07-4794-49b0-b4c2-510216214e1c",
   "metadata": {},
   "source": [
    "### 自动判断（不一定准，个人不太推荐）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "222beb38-f20c-4d62-a9a4-ba890c7fee95",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.convert_dtypes()\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdc8b656-f2ac-4634-af56-623849d01566",
   "metadata": {},
   "source": [
    "### 转换为类别型（category在机器学习中常用）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6eeffad6-2560-46ee-8785-4a280ec7310b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"category\"] = df[\"category\"].astype(\"category\")\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6991f26-a2bd-4b7c-91e2-3e5db3c56137",
   "metadata": {},
   "source": [
    "## 2.清洗\n",
    "### 缺失值处理（Missing Values）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6991161c-749d-46f0-9bf4-1f16ef3b8e0d",
   "metadata": {},
   "source": [
    "#### 1. 检查是否有缺失值 \n",
    "<p>在pandas中，表示缺失值的常见方式是 NaN（Not a Number），但也可以使用 None 或 pd.NA 来表示。检测这些缺失值的常用方法是使用 isnull() 或 isna() 这两个函数。 </p>\n",
    "表示缺失值<br>\n",
    "np.nan: NaN（Not a Number）是 用来表示数值类型缺失值最常见的方式。<br>\n",
    "None: Python 内置的 None 值在 pandas 中也可以被视为缺失值。<br>\n",
    "pd.NA: 这是 pandas 新引入的一种统一的缺失值标记，可以用于不同的数据类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "090a37bd-4add-4478-aacd-0507170a09c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      id    age   city\n",
      "0  False  False  False\n",
      "1  False   True  False\n",
      "2  False  False   True\n",
      "3  False   True  False\n",
      "4  False  False  False\n",
      "      id    age   city\n",
      "0  False  False  False\n",
      "1  False   True  False\n",
      "2  False  False   True\n",
      "3  False   True  False\n",
      "4  False  False  False\n",
      "id      0\n",
      "age     2\n",
      "city    1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"id\": [1, 2, 3, 4, 5],\n",
    "    \"age\": [25, None, 30, None,40],\n",
    "    \"city\": [\"Tokyo\", \"Osaka\", pd.NA, \"Kyoto\",np.nan]\n",
    "})\n",
    "\n",
    "print(df.isnull())\n",
    "print(df.isna())\n",
    "print(df.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "645e42e3-18e4-402c-950b-45d818b86a8f",
   "metadata": {},
   "source": [
    "#### 2. 缺失值处理方法"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a2c3ada-2b92-49c1-adef-279ce945fa39",
   "metadata": {},
   "source": [
    "  ##### 2.1 删除缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ee2caa8-e95e-44a0-85f1-67d23cdbd01d",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df)\n",
    "df_drop = df.dropna()\n",
    "print(\"=====after drop:=======\\n\")\n",
    "df_drop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5209a58-1987-4e62-a15b-7e909fd40de5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除有缺失值的列\n",
    "df_drop_col = df.dropna(axis=1)\n",
    "df_drop_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "101457d6-b78a-4393-8c4c-7ac4810ac791",
   "metadata": {},
   "outputs": [],
   "source": [
    "#条件删除：只删除某些列缺失的行\n",
    "df.dropna(subset=['age'], inplace=True)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f86eccb7-49bf-4d52-a3d0-9e071ca5ed0d",
   "metadata": {},
   "source": [
    "  ##### 2.2 填充缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42062b30-09de-435c-aec1-ea6fd2f2ecb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#使用固定值填充：\n",
    "df['age'] = df['age'].fillna(0)  # 填充为0\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f4705df-c766-44fa-a578-fdb8659ed5e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#使用平均值、中位数或众数填充：\n",
    "df['age'] = df['age'].fillna(df['age'].mean())\n",
    "df['city'] = df['city'].fillna(df['city'].mode()[0])\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "0d2eac16-5a11-4a87-9655-cac7ac4f9933",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id   age   city\n",
      "0   1  25.0  Tokyo\n",
      "1   2  30.0  Osaka\n",
      "2   3  30.0   <NA>\n",
      "3   4  40.0  Kyoto\n",
      "4   5  40.0  Tokyo\n"
     ]
    }
   ],
   "source": [
    "#前向填充 / 后向填充（适合时间序列）：\n",
    "#df['age'] = df['age'].fillna(method='ffill')  # 向前填充\n",
    "#df['age'] = df['age'].ffill()\n",
    "#print(df)\n",
    "#df['age'] = df['age'].fillna(method='bfill')  # 向后填充\n",
    "df['age'] = df['age'].bfill()\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3c8f3d8-7d93-42b2-a982-854619d8629b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#替换缺失值（替换特定值为 NaN）\n",
    "#replace函数的详细文档\n",
    "#https://pandas.pydata.org/pandas-docs/version/2.1.4/reference/api/pandas.DataFrame.replace.html\n",
    "import numpy as np\n",
    "df['city'] = df['city'].replace('Unknown', np.nan)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4eb5e480-f247-404d-8e3e-7fc2142fe958",
   "metadata": {},
   "source": [
    "### 重复值处理"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c9024aa-3d0f-445f-8d34-61fda2623b61",
   "metadata": {},
   "source": [
    "#### 1. 检测是否重复"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "508e3ba2-c296-44e4-919d-6d09c1bca008",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"order_id\": [1001, 1002, 1002, 1003, 1004, 1004],\n",
    "    \"customer\": [\"Alice\", \"Bob\", \"Bob\", \"Chris\", \"Alice\", \"Alice\"],\n",
    "    \"price\": [35.5, 42.0, 42.0, 28.0, 42.0, 42.0]\n",
    "})\n",
    "\n",
    "# 检查重复行，返回布尔 Series，True 表示该行是重复行（保留第一条）。\n",
    "#df.duplicated()\n",
    "# 仅根据 order_id 判断重复\n",
    "df.duplicated(subset=[\"order_id\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26c36a53-1854-4b74-941d-786b73a37cba",
   "metadata": {},
   "source": [
    "#### 2.  统计重复行数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "5aee67a3-593e-4dcf-b9d3-b07a2a8d2ab7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "重复行数量： 2\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(\"重复行数量：\", df.duplicated().sum())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9174529a-f5ba-4a9f-8a04-eac0ad7619b4",
   "metadata": {},
   "source": [
    "#### 3. 重复值的处理方法"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b268a1dd-5fa2-4c05-bdac-a965052b3a4b",
   "metadata": {},
   "source": [
    "##### 3.1. 删除重复行（保留第一条）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "ac9a9db0-c087-4ded-87d0-664896262ea1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alice</td>\n",
       "      <td>35.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Bob</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>Chris</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>Alice</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id customer  price\n",
       "0      1001    Alice   35.5\n",
       "1      1002      Bob   42.0\n",
       "3      1003    Chris   28.0\n",
       "4      1004    Alice   42.0"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_no_dup = df.drop_duplicates()\n",
    "df_no_dup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6502cbfe-3b25-4e3a-b98e-72e2226454b0",
   "metadata": {},
   "source": [
    "##### 3.2. 根据指定列删除重复"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "043df3b2-2871-4ca9-bffd-b4a63f82bd0d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alice</td>\n",
       "      <td>35.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Bob</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>Chris</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>Alice</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id customer  price\n",
       "0      1001    Alice   35.5\n",
       "1      1002      Bob   42.0\n",
       "3      1003    Chris   28.0\n",
       "4      1004    Alice   42.0"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_no_dup = df.drop_duplicates(subset=[\"order_id\"])\n",
    "df_no_dup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8070218a-2a81-410e-b85d-33cf1ef69427",
   "metadata": {},
   "source": [
    "##### 3.3. （不）保留重复记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "70575dbe-1511-4763-b3f4-b2317bb4fc4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alice</td>\n",
       "      <td>35.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>Chris</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id customer  price\n",
       "0      1001    Alice   35.5\n",
       "3      1003    Chris   28.0"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#保留最后一条重复记录\n",
    "df_no_dup = df.drop_duplicates(subset=[\"order_id\"], keep=\"last\")\n",
    "df_no_dup\n",
    "#删除所有重复行（不保留任何重复行）\n",
    "df_no_dup = df.drop_duplicates(subset=[\"order_id\"], keep=False)\n",
    "df[\"\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38355fb6-6fca-45ff-b3e7-f71bfcd2606d",
   "metadata": {},
   "source": [
    "##### 3.4  标记重复值\n",
    "不删除，只是标记重复行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "ecc0fe9b-afc8-4808-876b-dbef65f3f568",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer</th>\n",
       "      <th>price</th>\n",
       "      <th>is_duplicate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alice</td>\n",
       "      <td>35.5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>Bob</td>\n",
       "      <td>42.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1002</td>\n",
       "      <td>Bob</td>\n",
       "      <td>42.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1003</td>\n",
       "      <td>Chris</td>\n",
       "      <td>28.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1004</td>\n",
       "      <td>Alice</td>\n",
       "      <td>42.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1004</td>\n",
       "      <td>Alice</td>\n",
       "      <td>42.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id customer  price  is_duplicate\n",
       "0      1001    Alice   35.5         False\n",
       "1      1002      Bob   42.0         False\n",
       "2      1002      Bob   42.0          True\n",
       "3      1003    Chris   28.0         False\n",
       "4      1004    Alice   42.0         False\n",
       "5      1004    Alice   42.0          True"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['is_duplicate'] = df.duplicated(subset=[\"order_id\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd6aae3b-7ecf-4aed-bf92-0eaa82c7b191",
   "metadata": {},
   "source": [
    "##### 3.4  对重复值做统计或聚合\n",
    "有些情况下需要 合并重复行，而不是删除："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "65c565e8-4fe2-4e50-b95b-05e66fa72dae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>35.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  price\n",
       "0      1001   35.5\n",
       "1      1002   42.0\n",
       "2      1003   28.0\n",
       "3      1004   42.0"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对重复的 order_id 求 price 平均值\n",
    "df_grouped = df.groupby(\"order_id\", as_index=False)[\"price\"].mean()\n",
    "df_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31345e9a-3cff-4a33-9daf-4d49ea8d6f0a",
   "metadata": {},
   "source": [
    "##### 3.5  统计列中各个值出现的次数\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "ada0f907-c5af-4e45-a40f-6478c225508c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_id\n",
       "1002    2\n",
       "1004    2\n",
       "1001    1\n",
       "1003    1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计列order_id 中各个值的出现次数  \n",
    "counts = df['order_id'].value_counts()  \n",
    "counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47ea07d5-835d-4f52-98e3-2b85887896ea",
   "metadata": {},
   "source": [
    "## 数据标准化\n",
    "### 1. Min-Max 归一化\n",
    "例：根据房子的数据，看一下挑哪套好？希望价格越低越好，面积越大越好。 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "465262ea-d0e1-4ab1-919f-084c2562c09a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据：\n",
      "    house_id    price  area\n",
      "0       101   500000    50\n",
      "1       102  1200000   120\n",
      "2       103   750000    70\n",
      "3       104   950000    90\n",
      "4       105  2000000   200\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 原始数据\n",
    "df = pd.DataFrame({\n",
    "    \"house_id\": [101, 102, 103, 104, 105],\n",
    "    \"price\": [500000, 1200000, 750000, 950000, 2000000],  # 单位：元\n",
    "    \"area\": [50, 120, 70, 90, 200]  # 单位：平方米\n",
    "})\n",
    "\n",
    "print(\"原始数据：\\n\", df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "d826caf3-16f3-40c7-a3c9-5b7c6672da65",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Min-Max 归一化后的数据：\n",
      "    house_id     price      area\n",
      "0       101  0.000000  0.000000\n",
      "1       102  0.466667  0.466667\n",
      "2       103  0.166667  0.133333\n",
      "3       104  0.300000  0.266667\n",
      "4       105  1.000000  1.000000\n"
     ]
    }
   ],
   "source": [
    "# 对 price 和 area 列进行 Min-Max 归一化\n",
    "df_norm = df.copy()\n",
    "df_norm[['price', 'area']] = (df[['price', 'area']] - df[['price', 'area']].min()) / (df[['price', 'area']].max() - df[['price', 'area']].min())\n",
    "\n",
    "print(\"Min-Max 归一化后的数据：\\n\", df_norm)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6474476-f546-4ee5-b77c-8f23bb575e0e",
   "metadata": {},
   "source": [
    "给几套房子打一个综合评分："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "9512e75c-79a0-424c-9294-692a4f1f0032",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   house_id     score\n",
      "0       101  0.500000\n",
      "1       102  0.500000\n",
      "2       103  0.483333\n",
      "3       104  0.483333\n",
      "4       105  0.500000\n"
     ]
    }
   ],
   "source": [
    "df_norm['score'] = (1 - df_norm['price']) * 0.5 + df_norm['area'] * 0.5\n",
    "print(df_norm[['house_id', 'score']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cba96f3-37dd-4c5e-921c-a95cdae58ad3",
   "metadata": {},
   "source": [
    "### 2. Z-score标准化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "aa4d7ed1-2c19-4f2e-8850-e70b2aa4779e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据：\n",
      "     name   BMI  systolic_BP  blood_sugar\n",
      "0  Alice  22.5          120           90\n",
      "1    Bob  27.8          140          110\n",
      "2  Chris  31.2          150          160\n",
      "3  David  24.0          130           95\n",
      "4    Eva  29.5          135          120\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 原始健康指标数据\n",
    "df = pd.DataFrame({\n",
    "    \"name\": [\"Alice\", \"Bob\", \"Chris\", \"David\", \"Eva\"],\n",
    "    \"BMI\": [22.5, 27.8, 31.2, 24.0, 29.5],\n",
    "    \"systolic_BP\": [120, 140, 150, 130, 135],  # 收缩压 mmHg\n",
    "    \"blood_sugar\": [90, 110, 160, 95, 120]     # mg/dL\n",
    "})\n",
    "\n",
    "print(\"原始数据：\\n\", df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "ac496391-ca0f-4516-8a66-a8b36c4bfade",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Z-score 标准化结果：\n",
      "         BMI  systolic_BP  blood_sugar   name\n",
      "0 -1.227247    -1.341641    -0.898027  Alice\n",
      "1  0.218177     0.447214    -0.179605    Bob\n",
      "2  1.145431     1.341641     1.616448  Chris\n",
      "3 -0.818165    -0.447214    -0.718421  David\n",
      "4  0.681804     0.000000     0.179605    Eva\n"
     ]
    }
   ],
   "source": [
    "# 选择数值列\n",
    "health_features = ['BMI', 'systolic_BP', 'blood_sugar']\n",
    "\n",
    "# 计算 Z-score\n",
    "df_zscore = (df[health_features] - df[health_features].mean()) / df[health_features].std()\n",
    "\n",
    "# 添加姓名列\n",
    "df_zscore['name'] = df['name']\n",
    "print(\"Z-score 标准化结果：\\n\", df_zscore)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "b425d95a-1b42-41c7-861e-1d1f46d673da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name   BMI  systolic_BP  blood_sugar  health_index\n",
      "0  Alice  22.5          120           90      0.172553\n",
      "1    Bob  27.8          140          110      0.964043\n",
      "2  Chris  31.2          150          160      0.000000\n",
      "3  David  24.0          130           95      0.655367\n",
      "4    Eva  29.5          135          120      1.000000\n"
     ]
    }
   ],
   "source": [
    "#计算健康指数\n",
    "# 设定权重（可调整）：BMI 30%，血压 40%，血糖 30%\n",
    "weights = {'BMI': 0.3, 'systolic_BP': 0.4, 'blood_sugar': 0.3}\n",
    "\n",
    "# 用Z-score的绝对值衡量偏离程度\n",
    "df['health_index'] = 1 - (\n",
    "    abs(df_zscore['BMI']) * weights['BMI'] +\n",
    "    abs(df_zscore['systolic_BP']) * weights['systolic_BP'] +\n",
    "    abs(df_zscore['blood_sugar']) * weights['blood_sugar']\n",
    ")\n",
    "\n",
    "# Step 3: 归一化到0~1之间（防止出现负值）\n",
    "df['health_index'] = (df['health_index'] - df['health_index'].min()) / (df['health_index'].max() - df['health_index'].min())\n",
    "\n",
    "# 查看结果\n",
    "print(df[['name', 'BMI', 'systolic_BP', 'blood_sugar', 'health_index']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38259df8-f165-408e-9401-f4621d37f19b",
   "metadata": {},
   "source": [
    "## 编码\n",
    "### 1. 标签编码 （Label Encoding）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ccbdd662-2639-4e2f-9791-2fe486931f34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  gender  gender_encoded\n",
      "0      男               2\n",
      "1      女               1\n",
      "2      女               1\n",
      "3      男               2\n",
      "4      女               1\n",
      "5     中性               0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'gender': ['男', '女', '女', '男', '女','中性']\n",
    "})\n",
    "\n",
    "# 转换为数字\n",
    "df['gender_encoded'] = df['gender'].astype('category').cat.codes\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4088feca-7de1-4255-9c14-8cbaca78079a",
   "metadata": {},
   "source": [
    "### 2. 独热编码（One-Hot Encoding）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9bb9b096-98a8-4046-9ae5-34f760fbb3e1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   city_东京  city_京都  city_大阪\n",
      "0     True    False    False\n",
      "1    False    False     True\n",
      "2    False     True    False\n",
      "3     True    False    False\n",
      "4    False    False     True\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'city': ['东京', '大阪', '京都', '东京', '大阪']\n",
    "})\n",
    "\n",
    "# 独热编码\n",
    "df_encoded = pd.get_dummies(df, columns=['city'])\n",
    "print(df_encoded)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9e18c3bf-c1b0-4a11-99f9-adb43f166aa8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Size  Color_Blue  Color_Green  Color_Red\n",
      "0    S       False        False       True\n",
      "1    M        True        False      False\n",
      "2    L       False         True      False\n",
      "3    S       False        False       True\n",
      "4    M        True        False      False\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = {'Color': ['Red', 'Blue', 'Green', 'Red', 'Blue'],\n",
    "        'Size': ['S', 'M', 'L', 'S', 'M']}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Apply get_dummies to the 'Color' column\n",
    "df_encoded = pd.get_dummies(df, columns=['Color'])\n",
    "print(df_encoded)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65f1f485-78fb-4347-9d58-ef63ab172454",
   "metadata": {},
   "source": [
    "### 3. 顺序编码（Ordinary Encoding）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "412916c1-66e8-4595-bca2-0dae5172bd49",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    'level': ['低', '中', '高', '中', '低']\n",
    "})\n",
    "\n",
    "# 自定义顺序\n",
    "order = {'低': 1, '中': 2, '高': 3}\n",
    "df['level_encoded'] = df['level'].map(order)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b25e8d7b-9bb8-4f8f-a472-2eb81568a8aa",
   "metadata": {},
   "source": [
    "#### 4. 二进制编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0cec23e0-6d45-4c7c-affd-5269d1d3fd0b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  city\n",
      "0   东京\n",
      "1   大阪\n",
      "2   京都\n",
      "3  名古屋\n",
      "4   札幌\n",
      "5   福冈\n",
      "6   横滨\n",
      "   city_0  city_1  city_2\n",
      "0       0       0       1\n",
      "1       0       1       0\n",
      "2       0       1       1\n",
      "3       1       0       0\n",
      "4       1       0       1\n",
      "5       1       1       0\n",
      "6       1       1       1\n"
     ]
    }
   ],
   "source": [
    "#pip install category_encoders\n",
    "import pandas as pd\n",
    "import category_encoders as ce\n",
    "\n",
    "# 示例数据\n",
    "df = pd.DataFrame({\n",
    "    'city': ['东京', '大阪', '京都', '名古屋', '札幌', '福冈', '横滨']\n",
    "})\n",
    "\n",
    "# 使用Binary Encoder\n",
    "encoder = ce.BinaryEncoder(cols=['city'])\n",
    "df_encoded = encoder.fit_transform(df)\n",
    "\n",
    "print(df)\n",
    "print(df_encoded)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ed23bdc-8dec-4d0e-b7e7-ca5772fa9d02",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
